﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace JYJ.AiYard.EntityFrameworkCore
{
    public class AiYardDbNPOIExcel
    {
        public bool ToExcel(DataTable table, DateTime BDate, DateTime EDate, string _title, string _sheetName, string _filePath)
        {
            FileStream fs = new FileStream(_filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            _sheetName = _sheetName=="" ? "sheet1" : _sheetName;
            ISheet sheet = workBook.CreateSheet(_sheetName);
            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(_title);
            TimeSpan r = EDate.Subtract(BDate);

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, (r.Days + 1) * 3 + 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            ICellStyle cellStyle2 = workBook.CreateCellStyle();
            IFont font2 = workBook.CreateFont();
            font2.FontName = "微软雅黑";
            font2.FontHeightInPoints = 12;
            cellStyle2.SetFont(font2);
            cellStyle2.VerticalAlignment = VerticalAlignment.Center;
            cellStyle2.Alignment = HorizontalAlignment.Center;

            ICellStyle cellStyle3 = workBook.CreateCellStyle();
            IFont font3 = workBook.CreateFont();
            font3.FontName = "宋体";
            font3.FontHeightInPoints = 10;
            cellStyle3.SetFont(font3);
            cellStyle3.VerticalAlignment = VerticalAlignment.Center;
            cellStyle3.Alignment = HorizontalAlignment.Center;
            //处理时区和序号字段
            row = sheet.CreateRow(1);
            row.CreateCell(0).SetCellValue("序号");
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
            row.Height = 350;
            row.Cells[0].CellStyle = cellStyle2;

            row.CreateCell(1).SetCellValue("时间区");
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
            row.Height = 350;

            row.Cells[1].CellStyle = cellStyle2;
            //处理groupHeader
            int i = 1;
            for (DateTime dt = BDate; dt <= EDate; dt = dt.AddDays(1))
            {
                row.CreateCell(3 * i - 1).SetCellValue(dt.ToString("MM-dd"));
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 3 * i - 1, 3 * i + 1));
                row.Height = 350;
                row.Cells[i + 1].CellStyle = cellStyle2;
                i++;
            }
            row = sheet.CreateRow(2);
            row.Height = 250;
            i = 1;
            for (DateTime dt = BDate; dt <= EDate; dt = dt.AddDays(1))
            {
                row.CreateCell(3 * i - 1).SetCellValue("进场");
                row.CreateCell(3 * i).SetCellValue("车位");
                row.CreateCell(3 * i + 1).SetCellValue("出场");
                row.Cells[3 * i - 3].CellStyle = cellStyle3;
                row.Cells[3 * i - 2].CellStyle = cellStyle3;
                row.Cells[3 * i - 1].CellStyle = cellStyle3;
                //row.Cells[3 * i ].CellStyle = cellStyle3;
                //row.Cells[3 * i + 1].CellStyle = cellStyle3;
                row.Height = 250;
                i++;
            }

            //设置格式
            ICellStyle styleDeciaml = workBook.CreateCellStyle();
            IDataFormat format1 = workBook.CreateDataFormat();
            styleDeciaml.DataFormat = format1.GetFormat(@"0.00");

            ICellStyle styleInt = workBook.CreateCellStyle();
            IDataFormat format3 = workBook.CreateDataFormat();
            styleInt.DataFormat = format3.GetFormat(@"0");

            ICellStyle styleDate = workBook.CreateCellStyle();
            IDataFormat format2 = workBook.CreateDataFormat();
            styleDate.DataFormat = format2.GetFormat(@"yyyy-mm-dd");
            //处理数据内容
            for (i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(3 + i);
                row.Height = 250;
                for (int j = 0; j <= (r.Days + 1) * 3 + 1; j++)
                {
                    ICell cell = row.CreateCell(j);

                    if (table.Columns[j].DataType == typeof(decimal))
                    {
                        double doubV = 0;
                        if (double.TryParse(table.Rows[i][j].ToString(), out doubV))
                        {
                            cell.SetCellValue(doubV);

                            cell.CellStyle = styleDeciaml;
                        }
                        else
                        {
                            cell.SetCellValue(doubV);
                        }

                    }
                    else if (table.Columns[j].DataType == typeof(int) || table.Columns[j].DataType == typeof(Int64))
                    {
                        int intV = 0;
                        if (int.TryParse(table.Rows[i][j].ToString(), out intV))
                        {
                            cell.SetCellValue(intV);

                            cell.CellStyle = styleInt;
                        }
                        else
                        {
                            cell.SetCellValue(intV);
                        }
                    }
                    else if (table.Columns[j].DataType == typeof(DateTime))
                    {

                        cell.CellStyle = styleDate;

                        DateTime doubV;

                        if (DateTime.TryParse(table.Rows[i][j].ToString(), out doubV))
                        {
                            cell.SetCellValue(doubV);

                            cell.CellStyle = styleDate;
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }


                    }
                    else
                    {
                        cell.SetCellValue(table.Rows[i][j].ToString());
                    }
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string _title, string _sheetName, string _filePath, bool hadtitle,bool hadColtitle=true)
        {
            FileStream fs = new FileStream(_filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            _sheetName = _sheetName=="" ? "sheet1" : _sheetName;


            ISheet sheet = workBook.CreateSheet(_sheetName);
            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            //处理表格标题
            IRow row;
            int rows = 0;
            if (hadtitle)
            {
                row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue(_title);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                row.Height = 500; 
                row.Cells[0].CellStyle = cellStyle;
                rows = 1;
            }
            //处理表格列头
            if (hadColtitle)
            {
                row = sheet.CreateRow(1);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }
                rows = 2;
            }
            //设置格式
            ICellStyle styleDeciaml = workBook.CreateCellStyle();
            IDataFormat format1 = workBook.CreateDataFormat();
            styleDeciaml.DataFormat = format1.GetFormat(@"0.00");

            ICellStyle styleInt = workBook.CreateCellStyle();
            IDataFormat format3 = workBook.CreateDataFormat();
            styleInt.DataFormat = format3.GetFormat(@"0");

            ICellStyle styleDate = workBook.CreateCellStyle();
            IDataFormat format2 = workBook.CreateDataFormat();
            styleDate.DataFormat = format2.GetFormat(@"yyyy-mm-dd");
            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(rows + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);

                    if (table.Columns[j].DataType == typeof(decimal))
                    {
                        double doubV = 0;
                        if (double.TryParse(table.Rows[i][j].ToString(), out doubV))
                        {
                            cell.SetCellValue(doubV);

                            cell.CellStyle = styleDeciaml;
                        }
                        else
                        {
                            cell.SetCellValue(doubV);
                        }

                    }
                    else if (table.Columns[j].DataType == typeof(int) || table.Columns[j].DataType == typeof(Int64))
                    {
                        int intV = 0;
                        if (int.TryParse(table.Rows[i][j].ToString(), out intV))
                        {
                            cell.SetCellValue(intV);

                            cell.CellStyle = styleInt;
                        }
                        else
                        {
                            cell.SetCellValue(intV);
                        }
                    }
                    else if (table.Columns[j].DataType == typeof(DateTime))
                    {

                        cell.CellStyle = styleDate;

                        DateTime doubV;

                        if (DateTime.TryParse(table.Rows[i][j].ToString(), out doubV))
                        {
                            cell.SetCellValue(doubV);

                            cell.CellStyle = styleDate;
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }


                    }
                    else
                    {
                        if (!hadColtitle)
                        {
                            if (table.Rows[i][j] != null && table.Rows[i][j].ToString() != "")
                            {
                                double v = 0;
                                if (double.TryParse(table.Rows[i][j].ToString(), out v))
                                {
                                    cell.SetCellValue(v);
                                }
                                else
                                {
                                    cell.SetCellValue(table.Rows[i][j].ToString());
                                }
                            }
                            else
                            {
                                cell.SetCellValue(table.Rows[i][j].ToString());
                            }
                        }
                        else
                        {
                            cell.SetCellValue(table.Rows[i][j].ToString());
                        }
                    }
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath)
        {
            //this._title = title;
            //this._sheetName = sheetName;
            //this._filePath = filePath;
            return ToExcel(table, title, sheetName, filePath, true);
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath,bool Nocol)
        {
            //this._title = title;
            //this._sheetName = sheetName;
            //this._filePath = filePath;
            return ToExcel(table, title, sheetName, filePath, true, Nocol);
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath, DateTime BDate, DateTime EDate)
        {
            //this._title = title;
            //this._sheetName = sheetName;
            //this._filePath = filePath;
            return ToExcel(table, BDate, EDate, title, sheetName, filePath);
        }
    }
}
